package swd.work.trans_schema_form;

import com.pub.database.*;
import com.pub.web.WebFunction;
import com.pub.tools.*;

import swd.base.SwdBase;

import java.util.*;

public class Business extends SwdBase{
    QueryBean gqbnDAO=null;
    String gstrDBAlias=null;
    public Business(QueryBean pqbnDAO,String pstrDBAlias){
        super(pqbnDAO);
        gqbnDAO=pqbnDAO;
        gstrDBAlias=pstrDBAlias;
   }
   public String getSysNoList(String pstrDefaultSys){
       return WebFunction.transferRSToHtmlOption(gqbnDAO,"select sys_no,sys_no " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " ' ' " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " sys_name from wkf_sys order by sys_no",pstrDefaultSys);
   }
   public String getDbfNoList(String pstrSysNo,String pstrDbfNo){
       return WebFunction.transferRSToHtmlOption(gqbnDAO,"select dbf_no,dbf_no " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " ' ' " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " dbf_name from wkf_table where sys_no='" + pstrSysNo + "' order by dbf_no",pstrDbfNo);
   }
   public String getFields(String pstrTableName) throws Exception{
       StringBuffer lstbXml=new StringBuffer();
       Hashtable lhtFields=null;
       Hashtable lhtData=null;
       try{
           lhtFields=gqbnDAO.hashtableSQL("select fld_no,fld_name,fld_kind from wkf_layout where dbf_no='" + pstrTableName + "' order by seqno");

           if(lhtFields==null){
               return (new View()).getMessage("no_schema");
           }
           else{
               lstbXml.append("<TABLE>");
           }

           for(int i=0;i<lhtFields.size();i++){
               lhtData=((Hashtable)lhtFields.get(String.valueOf(i)));
               lstbXml.append("<TR>");
               lstbXml.append("<TD>");
               lstbXml.append("<input type='checkbox' name='trans_fields' value='");
               lstbXml.append(lhtData.get("FLD_NO"));
               lstbXml.append("'>");
               lstbXml.append("</TD>");
               lstbXml.append("<TD>");
               lstbXml.append(lhtData.get("FLD_NO"));
               lstbXml.append("</TD>");
               lstbXml.append("<TD>");
               lstbXml.append(lhtData.get("FLD_NAME"));
               lstbXml.append("</TD>");
               lstbXml.append("</TR>");
           }

           lstbXml.append("<TR><TD></TD><TD></TD><TD></TD></TR>");
           lstbXml.append("<TR><TD colspan='3'><input type='button' value='Select All' onclick=\"for(i=0;i<document.forms[0].trans_fields.length;i++){document.forms[0].trans_fields[i].checked=true;}\"/><input type='button' value='Unselect All' onclick=\"for(i=0;i<document.forms[0].trans_fields.length;i++){document.forms[0].trans_fields[i].checked=false;}\"/></TD></TR>");
           lstbXml.append("</table>");

           return lstbXml.toString().trim();
       }
       catch(Exception e){
           throw new Exception(e);
       }
       finally{
           if(lhtData!=null){
               lhtData.clear();
               lhtData = null;
           }
           if(lhtFields!=null){
               lhtFields.clear();
               lhtFields = null;
           }
       }

   }

   public String getTables(String pstrSysNo){
       ArrayList laltTableList=null;
       String lstrResult=null;
       try{
           laltTableList=gqbnDAO.querySQL("select wkf_table.dbf_no " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " '/' " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " wkf_table.dbf_name from wkf_sys_table,wkf_table where wkf_sys_table.dbf_no=wkf_table.dbf_no and wkf_sys_table.sys_no='" + pstrSysNo + "'");
           lstrResult=laltTableList.toString();
           return PublicFunction.replace(PublicFunction.replace(lstrResult,"[","",false),"]","",false);
       }
       catch(Exception e){
           return "null";
       }
       finally{
           if(laltTableList!=null){
               laltTableList.clear();
               laltTableList = null;
           }
       }
   }

   public String getPrgList(String pstrSysNo){
       ArrayList laltPrgList=null;
       String lstrResult=null;
       try{
           laltPrgList=gqbnDAO.querySQL("select wkf_prg.prg_no " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " '/' " + DBFunction.getConStrSybtax(DBFunction.getDBType(gstrDBAlias)) + " wkf_prg.prg_name from wkf_prg,wkf_sys_prg where wkf_prg.prg_no=wkf_sys_prg.prg_no and wkf_sys_prg.sys_no='" + pstrSysNo + "'");
           lstrResult=laltPrgList.toString();
           return PublicFunction.replace(PublicFunction.replace(lstrResult,"[","",false),"]","",false);
       }
       catch(Exception e){
           return "null";
       }
       finally{
           if(laltPrgList!=null){
               laltPrgList.clear();
               laltPrgList = null;
           }
       }
   }

   public String transferSchema(String pstrDbfNo,String[] pstrFldNo,String pstrPrgNo,String pstrFrmNo) throws Exception{
       ArrayList laltSQL=new ArrayList();
       StringBuffer lstbSQL=new StringBuffer();
       Hashtable lhtWkfLayout=null;
       int lintMaxSeqno=1;

       try{
           //先判斷wkf_frm0是否有資料
           if(gqbnDAO.readSingleValueData("select " + DBFunction.getNullToValueSyntax(DBFunction.getDBType(gstrDBAlias),"count(*)","0") + " from wkf_frm0 where prg_no='" + pstrPrgNo + "' and frm_no='" + pstrFrmNo + "'").equals("0")){
               //gqbnDAO.executeSQL("insert into wkf_frm0(prg_no,seqno,record_kind,frm_no,dbf_no) values('" + pstrPrgNo + "','" + pstrFrmNo + "','" + pstrDbfNo + "')");
               lstbSQL.append("insert into wkf_frm0(prg_no,seqno,record_kind,frm_no,dbf_no,frm_name) ");
               lstbSQL.append("select '");
               lstbSQL.append(pstrPrgNo);
               lstbSQL.append("',");
               //這一行其實是組成trim(to_char(nvl(max(seqno)+1,1),'0000'))，會這樣寫是考慮跨資料庫的語法不同
               lstbSQL.append(DBFunction.getTrimSyntax(DBFunction.getDBType(gstrDBAlias),DBFunction.getDateToStrSyntax(DBFunction.getDBType(gstrDBAlias),DBFunction.getNullToValueSyntax(DBFunction.getDBType(gstrDBAlias),"max(seqno)+1","1"),"0000")));
               lstbSQL.append(",'S','");
               lstbSQL.append(pstrFrmNo);
               lstbSQL.append("','");
               lstbSQL.append(pstrDbfNo);
               lstbSQL.append("','");
               lstbSQL.append(pstrPrgNo);
               lstbSQL.append("' from wkf_frm0 where prg_no='");
               lstbSQL.append(pstrPrgNo);
               lstbSQL.append("'");

               gqbnDAO.executeSQL(lstbSQL.toString());

               lstbSQL.delete(0,lstbSQL.length());
           }

           lintMaxSeqno=Integer.parseInt(gqbnDAO.readSingleValueData("select " + DBFunction.getNullToValueSyntax(DBFunction.getDBType(gstrDBAlias),"max(seqno)","0") + " from wkf_frm1 where prg_no='" + pstrPrgNo + "' and frm_no='" + pstrFrmNo + "'"));

           lhtWkfLayout=gqbnDAO.readSingleRowData("select fld_name,fld_no,fld_kind,wide+wide_dec as fld_len from wkf_layout where dbf_no='" + pstrDbfNo + "' order by seqno");

           //將要寫入wkf_frm1的資訊讀出並兜成inser into sql
           for(int i=0;i<pstrFldNo.length;i++){
               lhtWkfLayout=gqbnDAO.readSingleRowData("select fld_name,fld_no,fld_kind,wide+wide_dec as fld_len from wkf_layout where dbf_no='" + pstrDbfNo + "' and fld_no='" + pstrFldNo[i] + "'");
               lstbSQL.append("insert into wkf_frm1(prg_no,frm_no,seqno,fld_title_c,fld_title_e,fld_no,rw_mode,fld_kind,fld_len,fld_max_len) values ('");
               lstbSQL.append(pstrPrgNo);
               lstbSQL.append("','");
               lstbSQL.append(pstrFrmNo);
               lstbSQL.append("','");
               lstbSQL.append(FormatFunction.addZeros(String.valueOf(++lintMaxSeqno),WKF_LAYOUT_SEQNO_LEN));
               lstbSQL.append("','");
               lstbSQL.append(lhtWkfLayout.get("FLD_NAME"));//fld_title_c
               lstbSQL.append("','");
               lstbSQL.append(lhtWkfLayout.get("FLD_NO"));//fld_title_e
               lstbSQL.append("','");
               lstbSQL.append(lhtWkfLayout.get("FLD_NO"));//fld_no
               lstbSQL.append("','");
               lstbSQL.append("T");//rw_mode
               lstbSQL.append("','");
               lstbSQL.append(lhtWkfLayout.get("FLD_KIND"));//fld_kind
               lstbSQL.append("','");
               lstbSQL.append(lhtWkfLayout.get("FLD_LEN"));//fld_len
               lstbSQL.append("','");
               lstbSQL.append(lhtWkfLayout.get("FLD_LEN"));//fld_max_len
               lstbSQL.append("')");

               laltSQL.add(lstbSQL.toString());

               lstbSQL.delete(0,lstbSQL.length());
           }

           gqbnDAO.executeSQL(laltSQL);

           return new String(String.valueOf(laltSQL.size()));
       }
       catch(Exception e){
           e.printStackTrace();
           throw new Exception(e);
       }
       finally{
           if(laltSQL!=null){
               laltSQL.clear();
               laltSQL=null;
           }
       }
   }

   public String checkFrmExists(String pstrPrgNo,String pstrFrmNo) throws Exception{
       return gqbnDAO.readSingleValueData("select count(prg_no) from wkf_frm0 where prg_no='" + pstrPrgNo + "' and frm_no='" + pstrFrmNo.trim() + "'");
   }

   public String getUnUseFrmNo(String pstrPrgNo) throws Exception{
       return String.valueOf(PublicFunction.String_to_int(gqbnDAO.readSingleValueData("select max(frm_no) from wkf_frm0 where prg_no='" + pstrPrgNo + "'"))+1);
   }
}
